package photostock.control;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Hashtable;
import java.util.Vector;

public class DBPool {

    private volatile static DBPool instance;
    private volatile Vector<Connection> connectionPool;
    private volatile Vector<Boolean> busyPool;
    private String url = "jdbc:mysql://localhost/Photostock?user=root&password=atreides";
    
    
    private DBPool() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connectionPool = new Vector<Connection>();
            busyPool = new Vector<Boolean>();
            connectionPool.add(DriverManager.getConnection(this.url));
            busyPool.add(new Boolean(false));
            connectionPool.add(DriverManager.getConnection(this.url));
            busyPool.add(new Boolean(false));
            connectionPool.add(DriverManager.getConnection(this.url));
            busyPool.add(new Boolean(false));
            connectionPool.add(DriverManager.getConnection(this.url));
            busyPool.add(new Boolean(false));
            connectionPool.add(DriverManager.getConnection(this.url));
            busyPool.add(new Boolean(false));
        } catch (Exception e) {
            System.out.println(e.getClass().getName() + " levee : " + e.getMessage());
        }
    }
    
    public static DBPool getInstance() {
        if (null == instance) {
            synchronized(DBPool.class) {
                if (null == instance) instance = new DBPool();
            }
        }
        return instance;
    }
    
    public synchronized Connection getConnection() {
        for (int i=0; i<busyPool.size(); i++) {
            if (!busyPool.elementAt(i).booleanValue()) {
                busyPool.setElementAt(new Boolean(true), i);
                return connectionPool.elementAt(i); 
            }
        }
        return null;
    }
    
    public synchronized void closeConnection(Connection con) {
        for (int i=0; i<connectionPool.size(); i++) {
            if (connectionPool.elementAt(i) == con) {
                busyPool.setElementAt(new Boolean(false), i);
            }
        }
    }
    
    public synchronized boolean isBusy(Connection con) {
        boolean res = false;
        
        for (int i=0; i<connectionPool.size(); i++) 
            if (connectionPool.elementAt(i) == con)
                if (busyPool.elementAt(i).booleanValue()) res = true;
        
        return res;
    }
    
    public synchronized boolean execute(String request) {
        
        boolean res = false;
        Connection con = this.getConnection();
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(request);
            int i = ps.executeUpdate();
            res = i == 0 ? false : true;
        } catch (Exception e) {
            res = false;
            System.out.println(e.getClass().getName() + " levee : " + e.getMessage());
        } finally {
            try {
                ps.close();
            } catch (Exception e) {}
            this.closeConnection(con);
        }
        return res;
    }
    
    public synchronized Vector<Hashtable<String, Object>> getResult(String request) {
        
        Vector<Hashtable<String, Object>> res = new Vector<Hashtable<String, Object>>();
        Connection con = this.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            
            ps = con.prepareStatement(request);
            rs = ps.executeQuery();

            while(rs.next()) {
                
                ResultSetMetaData data = rs.getMetaData();
                Hashtable<String, Object> table = new Hashtable<String, Object>();
                for (int i=1; i<=data.getColumnCount(); i++) {
                    table.put(data.getColumnName(i), rs.getObject(i));
                }
                res.add(table);
            }
        } catch (Exception e) {
            System.out.println(e.getClass().getName() + " levee : " + e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {}
            try {
                ps.close();
            } catch (Exception e) {}
            this.closeConnection(con);
        }
        return res;
    }
    
}
